﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Diagnostics;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Data;
using System.Data.SqlClient;

public partial class BCNHBienSoXe : System.Web.UI.Page
{
    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    string frmName = "frmBaoCaoNhapHang";
    static int f_SuDung = 0, f_XemTatCa = 0, f_InBaoCao = 0,f_Khoa=0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static int PageNumber = 10;
    static int CurrentPage = 1;
    static double klmuaVAT = 0, klmuaKVAT = 0, klthucte = 0, tlhang = 0, klsauquidoi = 0, giavlvat = 0, giavlkhongvat = 0, ttvlcovat = 0, ttvlkvat = 0, cvccovat = 0, cvckvt = 0, ttcvccovat = 0, ttcvckvat = 0, tt1m3covat = 0, tt1m3kvat = 0, tt1chuyencovat = 0, tt1chuyenkvat = 0;
    static string ncc = "";
    static System.Data.DataTable dt;
    System.Data.DataTable dtPrint;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Convert.ToString(Session["user"])) || string.IsNullOrEmpty(Convert.ToString(Session["IDUser"])))
            Response.Redirect("Default.aspx");

        if (!IsPostBack)
        {
            string idnguoidung = Session["IDUser"].ToString();
            string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa,Khoa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

            System.Data.DataTable dt2 = ex.GetData_Text(sql);
            if (dt2.Rows.Count > 0)
            {
                f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
                f_Khoa = int.Parse(dt2.Rows[0]["Khoa"].ToString());
            }

            CurrentPage = 1;
            Title = "Báo cáo tổng hợp nhập hàng | " + Header;
            for (int i = 1; i <= 12; i++)
            {
                dlThang.Items.Add(new ListItem("Tháng " + i.ToString(), i.ToString()));
            }
            dlThang.Items.Insert(0, new ListItem("Theo thời gian", ""));
            dlThang.SelectedIndex = 0;

            int year1 = DateTime.Now.Year - 3;
            int year2 = DateTime.Now.Year;
            for (int i = year2; i >= year1; i--)
            {
                dlNam.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));
            }
            dlNam.SelectedValue = year2.ToString();

            txtTuNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            txtDenNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            dlNam.Visible = false;
            dlBienSoText.Visible = false;
            LoadBenChayCuoc(dlBenChayCuoc, "--Chọn bên chạy cước--");
            LoadBienSo(dlBienSo, "--Chọn biển số--");
            LoadTramTron(dlTramTron);
            //lbTrangThai.Visible = false;
            //btnKhoa.Visible = false;
        }
    }
    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }
    protected void btnPrint_Click(object sender, EventArgs e)
    {
        if (f_InBaoCao.Equals(1))
        {
            if (dlBenChayCuoc.SelectedIndex > 0 && dlBienSo.SelectedIndex.Equals(0) && dlBienSoText.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
            {
                SqlParameter[] p1 = new SqlParameter[3];
                p1[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                p1[0].SqlDbType = SqlDbType.SmallDateTime;
                p1[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                p1[1].SqlDbType = SqlDbType.SmallDateTime;
                p1[2] = new SqlParameter("@BenChayCuoc", ex.GetGuid(dlBenChayCuoc.SelectedValue));
                p1[2].SqlDbType = SqlDbType.UniqueIdentifier;

                dtPrint = ex.GetData("sp_BCNH_BSX_ByBenChayCuoc_Print", p1);

            }
            else
                if (dlBenChayCuoc.SelectedIndex > 0 && dlBienSo.SelectedIndex > 0 && dlBienSoText.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                {
                    SqlParameter[] p1 = new SqlParameter[4];
                    p1[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                    p1[0].SqlDbType = SqlDbType.SmallDateTime;
                    p1[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                    p1[1].SqlDbType = SqlDbType.SmallDateTime;
                    p1[2] = new SqlParameter("@BenChayCuoc", ex.GetGuid(dlBenChayCuoc.SelectedValue));
                    p1[2].SqlDbType = SqlDbType.UniqueIdentifier;
                    p1[3] = new SqlParameter("@BienSo", ex.GetGuid(dlBienSo.SelectedValue));
                    p1[3].SqlDbType = SqlDbType.UniqueIdentifier;
                    dtPrint = ex.GetData("sp_BCNH_BSX_ByBenChayCuocBSX_Print", p1);
                }
                else
                    if (dlBenChayCuoc.SelectedIndex > 0 && dlBienSo.SelectedIndex.Equals(0) && dlBienSoText.SelectedIndex > 0 && dlTramTron.SelectedIndex.Equals(0))
                    {
                        SqlParameter[] p1 = new SqlParameter[4];
                        p1[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                        p1[0].SqlDbType = SqlDbType.SmallDateTime;
                        p1[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                        p1[1].SqlDbType = SqlDbType.SmallDateTime;
                        p1[2] = new SqlParameter("@BenChayCuoc", ex.GetGuid(dlBenChayCuoc.SelectedValue));
                        p1[2].SqlDbType = SqlDbType.UniqueIdentifier;
                        p1[3] = new SqlParameter("@BienSo", dlBienSoText.SelectedItem.Text.Trim());
                        p1[3].SqlDbType = SqlDbType.NVarChar;
                        dtPrint = ex.GetData("sp_BCNH_BSX_ByBenChayCuocBSXText_Print", p1);
                    }
                try
                {
                    if (dtPrint.Rows.Count > 0)
                    {
                        Process[] processBefore = Process.GetProcessesByName("excel");
                        Microsoft.Office.Interop.Excel.ApplicationClass exc = new Microsoft.Office.Interop.Excel.ApplicationClass();
                        object missvalues = System.Reflection.Missing.Value;
                        string checkpath = Server.MapPath("Excel/BCNH_TongHop.xls");
                        Microsoft.Office.Interop.Excel.Workbook wb = exc.Workbooks.Open(checkpath, 0, true, 3, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

                        exc.Visible = false;
                        exc.StandardFont = "Times New Roman";
                        exc.StandardFontSize = 13;

                        string bcc = dlBenChayCuoc.SelectedIndex.Equals(0) ? "TheoBienSo" : dlBenChayCuoc.SelectedItem.Text;
                        string thang = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text : txtTuNgay.Text.Replace('/', '.') + "-" + txtDenNgay.Text.Replace('/', '.');

                        Process[] processAffre = Process.GetProcessesByName("excel");
                        int processID = 0;
                        foreach (Process pro in processAffre)
                        {
                            if (!processBefore.Select(p => p.Id).Contains(pro.Id))
                                processID = pro.Id;
                        }


                        int stt = dtPrint.Rows.Count;
                        Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];

                        #region TongHop
                        ws.Name = "Bảng tổng hợp";
                        Microsoft.Office.Interop.Excel.Range Border;

                        string tieudethang1 = dlThang.SelectedIndex.Equals(0) ? "TỪ " + txtTuNgay.Text + " - " + txtDenNgay.Text : dlThang.SelectedItem.Text.ToUpper() + " " + dlNam.SelectedItem.Text.ToUpper();

                        ws.get_Range("A3", "M3").Merge(true);
                        Microsoft.Office.Interop.Excel.Range a4 = ws.get_Range("A3", "M3");
                        a4.FormulaR1C1 = "BÁO CÁO SẢN LƯỢNG NHẬP " + tieudethang1;
                        a4.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                        a4.Font.Size = 18;
                        a4.EntireColumn.RowHeight = 23;
                        a4.Font.Name = "Times New Roman";
                        a4.Font.Bold = true;



                        ws.get_Range("A4", "M4").Merge(true);
                        Microsoft.Office.Interop.Excel.Range a4m42 = ws.get_Range("A4", "M4");
                        a4m42.FormulaR1C1 = dlBenChayCuoc.SelectedIndex > 0 ? "Bên chạy cước: " + dlBenChayCuoc.SelectedItem.Text : "Theo từng biển số";
                        a4m42.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                        a4m42.Font.Size = 14;
                        a4m42.EntireColumn.RowHeight = 23;
                        a4m42.Font.Name = "Times New Roman";
                        a4m42.Font.Bold = true;


                        int si = 7;
                        for (int i = 0; i < stt; i++)
                        {
                            Border = ws.get_Range("A" + (si), "X" + (si + i + 1));
                            Border.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);
                            #region Cells
                            ws.Cells[si + i, 1] = HttpUtility.HtmlDecode((i + 1).ToString());
                            ws.Cells[si + i, 2] = HttpUtility.HtmlDecode(dtPrint.Rows[i][0].ToString());
                            ws.Cells[si + i, 3] = "'" + HttpUtility.HtmlDecode(dtPrint.Rows[i][1].ToString());
                            ws.Cells[si + i, 4] = HttpUtility.HtmlDecode(dtPrint.Rows[i][2].ToString());
                            ws.Cells[si + i, 5] = HttpUtility.HtmlDecode(dtPrint.Rows[i][3].ToString());
                            ws.Cells[si + i, 6] = HttpUtility.HtmlDecode(dtPrint.Rows[i][4].ToString());
                            ws.Cells[si + i, 7] = HttpUtility.HtmlDecode(dtPrint.Rows[i][5].ToString());
                            ws.Cells[si + i, 8] = HttpUtility.HtmlDecode(dtPrint.Rows[i][6].ToString());
                            ws.Cells[si + i, 9] = HttpUtility.HtmlDecode(dtPrint.Rows[i][7].ToString());
                            ws.Cells[si + i, 10] = HttpUtility.HtmlDecode(dtPrint.Rows[i][8].ToString());
                            ws.Cells[si + i, 11] = HttpUtility.HtmlDecode(dtPrint.Rows[i][9].ToString());
                            ws.Cells[si + i, 12] = HttpUtility.HtmlDecode(dtPrint.Rows[i][10].ToString());
                            ws.Cells[si + i, 13] = HttpUtility.HtmlDecode(dtPrint.Rows[i][11].ToString());
                            ws.Cells[si + i, 14] = HttpUtility.HtmlDecode(dtPrint.Rows[i][12].ToString());
                            ws.Cells[si + i, 15] = HttpUtility.HtmlDecode(dtPrint.Rows[i][13].ToString());
                            ws.Cells[si + i, 16] = HttpUtility.HtmlDecode(dtPrint.Rows[i][14].ToString());
                            ws.Cells[si + i, 17] = HttpUtility.HtmlDecode(dtPrint.Rows[i][15].ToString());
                            ws.Cells[si + i, 18] = HttpUtility.HtmlDecode(dtPrint.Rows[i][16].ToString());
                            ws.Cells[si + i, 19] = HttpUtility.HtmlDecode(dtPrint.Rows[i][17].ToString());
                            ws.Cells[si + i, 20] = HttpUtility.HtmlDecode(dtPrint.Rows[i][18].ToString());
                            ws.Cells[si + i, 21] = HttpUtility.HtmlDecode(dtPrint.Rows[i][19].ToString());
                            ws.Cells[si + i, 22] = HttpUtility.HtmlDecode(dtPrint.Rows[i][20].ToString());
                            ws.Cells[si + i, 23] = HttpUtility.HtmlDecode(dtPrint.Rows[i][21].ToString());
                            ws.Cells[si + i, 24] = HttpUtility.HtmlDecode(dtPrint.Rows[i][22].ToString());

                            #endregion
                        }


                        Microsoft.Office.Interop.Excel.Range e2 = ws.get_Range("E" + (stt + 7).ToString(), "E" + (stt + 7).ToString());
                        e2.Formula = "Tổng cộng";
                        e2.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                        e2.Font.Bold = true;

                        string[] footer = { "H", "I", "J", "K", "L", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X" };

                        for (int k = 0; k <= 14; k++)
                        {
                            Microsoft.Office.Interop.Excel.Range h = ws.get_Range(footer[k] + (stt + 7).ToString(), footer[k] + (stt + 7).ToString());
                            h.Formula = "=SUM(" + footer[k] + "7:" + footer[k] + (6 + stt).ToString() + ")";
                            h.Font.Bold = true;
                        }

                        #endregion

                        si = 6;
                        int index = 0, stt2 = 0;
                        string lvl = "";
                        double tong1 = 0, tong2 = 0, tong3 = 0, tong4 = 0;
                        string congno = "";
                        if (dlBenChayCuoc.SelectedIndex > 0)
                        {

                            #region PrintBSX

                            for (int i = 0; i < stt; i++)
                            {
                                if (i > 0)
                                    lvl = HttpUtility.HtmlDecode(dtPrint.Rows[i - 1][5].ToString()).Replace(".", "").Replace("-", "").Replace(" ", "");

                                if (!lvl.Trim().Equals(HttpUtility.HtmlDecode(dtPrint.Rows[i][5].ToString()).Replace(".", "").Replace("-", "").Replace(" ", "")))
                                {

                                    if (i > 0)
                                    {
                                        Microsoft.Office.Interop.Excel.Range e22 = ws.get_Range("E" + (stt2 + 7).ToString(), "E" + (stt2 + 7).ToString());
                                        e22.Formula = "Tổng cộng";
                                        e22.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                                        e22.Font.Bold = true;

                                        string[] footer2 = { "H", "I", "J", "K", "L", "O", "P" };

                                        for (int k = 0; k <= 6; k++)
                                        {
                                            Microsoft.Office.Interop.Excel.Range h = ws.get_Range(footer2[k] + (stt2 + 7).ToString(), footer2[k] + (stt2 + 7).ToString());
                                            h.Formula = "=SUM(" + footer2[k] + "7:" + footer2[k] + (6 + stt2).ToString() + ")";
                                            h.Font.Bold = true;

                                        }
                                        congno += tong1.ToString() + "-" + tong2.ToString() + "-" + tong3.ToString() + "-" + tong4.ToString() + "@";

                                    }


                                    lvl = HttpUtility.HtmlDecode(dtPrint.Rows[i][5].ToString()).Replace(".", "").Replace("-", "").Replace(" ", "");
                                    ws = (Worksheet)exc.Worksheets.Add(missvalues, wb.Worksheets[wb.Worksheets.Count], missvalues, missvalues);
                                    ws.Name = lvl.Replace('*', 'x');
                                    congno += lvl.Replace('*', 'x') + "--";

                                    #region Header


                                    ws.get_Range("A1", "D1").Merge(true);
                                    Microsoft.Office.Interop.Excel.Range a1d1 = ws.get_Range("A1", "D1");
                                    a1d1.FormulaR1C1 = "Công ty cổ phần xây dựng Đức Anh";
                                    a1d1.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
                                    a1d1.Font.Size = 11;
                                    a1d1.Font.Name = "Times New Roman";
                                    a1d1.Font.Bold = true;

                                    ws.get_Range("A2", "D2").Merge(true);
                                    Microsoft.Office.Interop.Excel.Range a2d2 = ws.get_Range("A2", "D2");
                                    a2d2.FormulaR1C1 = "Cụm KCN Làng nghề Lâm Thao";
                                    a2d2.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
                                    a2d2.Font.Size = 11;
                                    a2d2.Font.Name = "Times New Roman";
                                    a2d2.Font.Bold = true;



                                    ws.get_Range("A3", "M3").Merge(true);
                                    Microsoft.Office.Interop.Excel.Range a4m4 = ws.get_Range("A3", "M3");
                                    a4m4.FormulaR1C1 = "BÁO CÁO SẢN LƯỢNG NHẬP " + tieudethang1;
                                    a4m4.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                                    a4m4.Font.Size = 18;
                                    a4m4.EntireColumn.RowHeight = 23;
                                    a4m4.Font.Name = "Times New Roman";
                                    a4m4.Font.Bold = true;

                                    ws.get_Range("A4", "M4").Merge(true);
                                    Microsoft.Office.Interop.Excel.Range a4m41 = ws.get_Range("A4", "M4");
                                    a4m41.FormulaR1C1 = "Biển số : " + lvl;
                                    a4m41.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                                    a4m41.Font.Size = 14;
                                    a4m41.EntireColumn.RowHeight = 23;
                                    a4m41.Font.Name = "Times New Roman";
                                    a4m41.Font.Bold = true;

                                    ws.get_Range("H5", "I5").Merge(true);
                                    Microsoft.Office.Interop.Excel.Range h5i5 = ws.get_Range("H5", "I5");
                                    h5i5.FormulaR1C1 = "Khối lượng mua";
                                    h5i5.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                                    h5i5.Font.Size = 11;
                                    h5i5.Font.Name = "Times New Roman";
                                    h5i5.Font.Bold = true;

                                    ws.get_Range("M5", "N5").Merge(true);
                                    Microsoft.Office.Interop.Excel.Range m5n5 = ws.get_Range("M5", "N5");
                                    m5n5.FormulaR1C1 = "Giá cước VC";
                                    m5n5.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                                    m5n5.Font.Size = 11;
                                    m5n5.Font.Name = "Times New Roman";
                                    m5n5.Font.Bold = true;

                                    ws.get_Range("O5", "P5").Merge(true);
                                    Microsoft.Office.Interop.Excel.Range o5p5 = ws.get_Range("O5", "P5");
                                    o5p5.FormulaR1C1 = "Thành tiền cước VC";
                                    o5p5.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                                    o5p5.Font.Size = 11;
                                    o5p5.Font.Name = "Times New Roman";
                                    o5p5.Font.Bold = true;


                                    Border = ws.get_Range("A5", "P6");
                                    Border.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);

                                    string[] header = { "STT", "Số phiếu", "Ngày nhập", "Loại vật liệu", "Bên bán", "Bên chạy cước", "Biển số xe", "Có VAT", "Không VAT", "KL thực tế", "TL hàng", "TL sau qui đổi", "Có VAT", "Không VAT", "Có VAT", "Không VAT" };
                                    string[] key = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P" };

                                    for (int n = 0; n <= 15; n++)
                                    {
                                        ws.get_Range(key[n] + "6", key[n] + "6").Merge(false);
                                        Microsoft.Office.Interop.Excel.Range a6a6 = ws.get_Range(key[n] + "6", key[n] + "6");
                                        a6a6.FormulaR1C1 = header[n];
                                        a6a6.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                                        a6a6.Font.Size = 11;
                                        a6a6.Font.Name = "Times New Roman";
                                        a6a6.Font.Bold = true;
                                    }

                                    #endregion

                                    #region SetWidth

                                    Microsoft.Office.Interop.Excel.Range a1 = ws.get_Range("A1", "A1");
                                    a1.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
                                    a1.EntireColumn.ColumnWidth = 6;

                                    Microsoft.Office.Interop.Excel.Range b1 = ws.get_Range("B1", "B1");
                                    b1.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
                                    b1.EntireColumn.ColumnWidth = 10;

                                    Microsoft.Office.Interop.Excel.Range c1 = ws.get_Range("C1", "C1");
                                    c1.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
                                    c1.EntireColumn.ColumnWidth = 12;

                                    Microsoft.Office.Interop.Excel.Range d1 = ws.get_Range("D1", "D1");
                                    d1.EntireColumn.ColumnWidth = 15;

                                    Microsoft.Office.Interop.Excel.Range e1 = ws.get_Range("E1", "E1");
                                    e1.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
                                    e1.EntireColumn.ColumnWidth = 24;

                                    Microsoft.Office.Interop.Excel.Range f1 = ws.get_Range("F1", "F1");
                                    f1.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
                                    f1.EntireColumn.ColumnWidth = 24;

                                    Microsoft.Office.Interop.Excel.Range g1 = ws.get_Range("G1", "G1");
                                    g1.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
                                    g1.EntireColumn.ColumnWidth = 15;

                                    #endregion
                                    si = 6;
                                    index = 0;
                                    stt2 = 0;
                                    tong4 = tong3 = tong2 = tong1 = 0;

                                }
                                //ghi du lieu vào sheet moi tao do
                                index++;

                                #region Cells
                                stt2++;

                                Microsoft.Office.Interop.Excel.Range cell11 = (Range)ws.Cells[si + index, 1];
                                cell11.Value2 = (stt2).ToString();
                                cell11.Font.Size = 11;
                                cell11.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;

                                tong1 += double.Parse(dtPrint.Rows[i][6].ToString().Trim().Equals("") ? "0" : dtPrint.Rows[i][6].ToString());
                                tong2 += double.Parse(dtPrint.Rows[i][7].ToString().Trim().Equals("") ? "0" : dtPrint.Rows[i][7].ToString());
                                tong3 += double.Parse(dtPrint.Rows[i][17].ToString().Trim().Equals("") ? "0" : dtPrint.Rows[i][17].ToString());
                                tong4 += double.Parse(dtPrint.Rows[i][18].ToString().Trim().Equals("") ? "0" : dtPrint.Rows[i][18].ToString());

                                for (int j = 2; j <= 16; j++)
                                {

                                    Border = ws.get_Range("A" + (si + index), "P" + (si + index + 1));

                                    Border.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);


                                    Microsoft.Office.Interop.Excel.Range cells = (Range)ws.Cells[si + index, j];
                                    cells.Font.Size = 11;

                                    if (j == 3)
                                        cells.Value2 = "'" + HttpUtility.HtmlDecode(dtPrint.Rows[i][j - 2].ToString());
                                    else
                                    {
                                        if (j >= 13 && j <= 16)
                                        {
                                            cells.Value2 = HttpUtility.HtmlDecode(dtPrint.Rows[i][j + 2].ToString());
                                        }
                                        else cells.Value2 = HttpUtility.HtmlDecode(dtPrint.Rows[i][j - 2].ToString());
                                    }

                                    if (j > 7)
                                    {
                                        cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlRight;
                                        cells.EntireColumn.ColumnWidth = 14;
                                    }
                                    else if (j <= 3)
                                        cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                                    else cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
                                }
                                #endregion

                                //tinh toan tong footer
                                if (i == (stt - 1))
                                {
                                    //In footer
                                    Microsoft.Office.Interop.Excel.Range e22 = ws.get_Range("E" + (stt2 + 7).ToString(), "E" + (stt2 + 7).ToString());
                                    e22.Formula = "Tổng cộng";
                                    e22.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                                    e22.Font.Bold = true;

                                    string[] footer2 = { "H", "I", "J", "K", "L", "O", "P" };

                                    for (int k = 0; k <= 6; k++)
                                    {
                                        Microsoft.Office.Interop.Excel.Range h = ws.get_Range(footer2[k] + (stt2 + 7).ToString(), footer2[k] + (stt2 + 7).ToString());
                                        h.Formula = "=SUM(" + footer2[k] + "7:" + footer2[k] + (6 + stt2).ToString() + ")";
                                        h.Font.Bold = true;
                                    }
                                    congno += tong1.ToString() + "-" + tong2.ToString() + "-" + tong3.ToString() + "-" + tong4.ToString() + "@";
                                }
                            }
                            #endregion

                        }


                        string path = Server.MapPath("tmp.xls");
                        FileInfo f = new FileInfo(path);
                        if (f.Exists) f.Delete();
                        wb.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, missvalues, missvalues, missvalues, missvalues, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missvalues, missvalues, missvalues, missvalues, missvalues);

                        wb.Close(true, missvalues, missvalues);
                        exc.Quit();
                        releaseObject(ws);
                        releaseObject(wb);
                        releaseObject(exc);
                        if (processID != 0)
                        {
                            Process process = Process.GetProcessById(processID);
                            process.Kill();
                        }

                        string filename = "BCNH-BienSoXe-" + bcc + "-" + thang;
                        HttpResponse response = HttpContext.Current.Response;
                        response.ClearContent();
                        response.ContentType = "application/vnd.ms-excel"; ;
                        response.AddHeader("Content-Disposition", "Attachment; filename=" + filename + ".xls;");

                        MemoryStream stream;
                        FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
                        byte[] img = new byte[fs.Length];
                        fs.Read(img, 0, Convert.ToInt32(fs.Length));
                        fs.Close();
                        fs.Dispose();
                        stream = new MemoryStream(img);
                        stream.WriteTo(Response.OutputStream);

                        if (f.Exists) f.Delete();
                        response.Flush();
                        response.End();
                    }

                }
                catch (Exception ax)
                {
                    gstGetMess(ax.Message, "");
                }
            //}
            //else
            //    gstGetMess("Chọn bên chạy cước", "");
        }
        else gstGetMess("Bạn không có quyền in báo cáo này", "");
    }
    protected void GV_RowCreated(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Header)
        {
            GridView HeaderGrid = (GridView)sender;
            GridViewRow HeaderGridRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);
            HeaderGridRow.Height = 20;
            HeaderGridRow.HorizontalAlign = HorizontalAlign.Center;
            TableCell HeaderCell = new TableCell();

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);


            HeaderCell = new TableCell();
            HeaderCell.Text = "KL mua";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);


            HeaderCell = new TableCell();
            HeaderCell.Text = "Giá vật liệu";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "Thành tiền VL";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "Giá cước vận chuyển";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "Thành tiền cước VC";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "Thành tiền /1m3";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "Thành tiền /1chuyến";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);



            GV.Controls[0].Controls.AddAt(0, HeaderGridRow);
        }
    }

    static public void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            throw new Exception("Exception Occured while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }

    void LoadBenChayCuoc(DropDownList dl, string title)
    {
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text));
        p[1].SqlDbType = SqlDbType.SmallDateTime;


        dl.Items.Clear();
        dl.DataSource = ex.GetData("sp_BCNH_LoadBenChayCuocByTime", p);
        dl.DataBind();
        dl.Items.Insert(0, new ListItem(title, ""));
        dl.SelectedIndex = 0;
    }
    void LoadBienSo(DropDownList dl, string title)
    {
        if (dlBenChayCuoc.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@BenChayCuoc", ex.GetGuid(dlBenChayCuoc.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;


            dl.Items.Clear();
            dl.DataSource = ex.GetData("sp_BCNH_LoadBienSoByBenChayCuoc", p);
            dl.DataBind();
            dl.Items.Insert(0, new ListItem(title, ""));
            dl.SelectedIndex = 0;
        }
        else
        {
            dl.Items.Clear();
            dl.Items.Insert(0, new ListItem(title, ""));
            dl.SelectedIndex = 0;
        }
    }
    void LoadBienSoText(DropDownList dl, string title)
    {
        if (dlBenChayCuoc.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@BenChayCuoc", ex.GetGuid(dlBenChayCuoc.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;


            dl.Items.Clear();
            dl.DataSource = ex.GetData("sp_BCNH_BSX_ListBienSoByBenChayCuoc", p);
            dl.DataBind();
            dl.Items.Insert(0, new ListItem(title, ""));
            dl.SelectedIndex = 0;
        }
        else
        {
            dl.Items.Clear();
            dl.Items.Insert(0, new ListItem(title, ""));
            dl.SelectedIndex = 0;
        }
    }
    void LoadTramTron(DropDownList dl)
    {
        dlTramTron.Items.Clear();
        dlTramTron.DataSource = ex.GetData("sp_xdanhaphang_ListTramTron");
        dlTramTron.DataBind();
        dlTramTron.Items.Insert(0, new ListItem("--Chọn trạm trộn--", ""));
        dlTramTron.SelectedIndex = 0;
    }
    protected void dlThang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            txtTuNgay.Visible = true;
            txtDenNgay.Visible = true;
            dlNam.Visible = false;
            //lbTrangThai.Visible = false;
            //btnKhoa.Visible = false;
        }
        else
        {
            txtTuNgay.Visible = false;
            txtDenNgay.Visible = false;
            dlNam.Visible = true;
            //lbTrangThai.Visible = true;
            //btnKhoa.Visible = true;
        }
       

        getDate();

        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;

        dlBenChayCuoc.Items.Clear();
        dlBenChayCuoc.DataSource = ex.GetData("sp_BCNH_LoadBenChayCuocByTime", p);
        dlBenChayCuoc.DataBind();
        dlBenChayCuoc.Items.Insert(0, new ListItem("--Chọn bên chạy cước--", ""));
        dlBenChayCuoc.SelectedIndex = 0;

        //if (dlThang.SelectedIndex > 0)
        //{
        //    //load trang thai khoa
        //    p = new SqlParameter[3];
        //    p[0] = new SqlParameter("@Name", "BCNH");
        //    p[0].SqlDbType = SqlDbType.NVarChar;
        //    p[1] = new SqlParameter("@Thang", int.Parse(dlThang.SelectedValue));
        //    p[1].SqlDbType = SqlDbType.Int;
        //    p[2] = new SqlParameter("@Nam", int.Parse(dlNam.SelectedValue));
        //    p[2].SqlDbType = SqlDbType.Int;

        //    string index = ex.GetValueString("sp_xdakhoa_Check", p);

        //    if (index.Trim().Equals("") || index.Trim().Equals("1"))
        //    {
        //        lbTrangThai.Text = "Trạng thái: Dữ liệu đang mở";
        //        btnKhoa.Text = "Khóa lại";
        //    }
        //    else
        //    {
        //        lbTrangThai.Text = "Trạng thái: Dữ liệu đang bị khóa";
        //        btnKhoa.Text = "Mở lại";
        //    }
        //}
        LoadBienSo(dlBienSo, "--Chọn biển số--");
    }
    public void getDate()
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
        else
        {
            txtTuNgay.Text = "1/" + dlThang.SelectedValue + "/" + dlNam.SelectedValue;
            txtDenNgay.Text = GetLastDayOfMonth(int.Parse(dlThang.SelectedValue)).ToString("dd/MM/yyyy");
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
    }
    public DateTime GetLastDayOfMonth(int iMonth)
    {
        DateTime dtResult = new DateTime(int.Parse(dlNam.SelectedValue), iMonth, 1);
        dtResult = dtResult.AddMonths(1);
        dtResult = dtResult.AddDays(-(dtResult.Day));
        return dtResult;
    }
    
    protected void btnXem_Click(object sender, EventArgs e)
    {
        if (f_SuDung.Equals(1))
        {
            CurrentPage = 1;
            btnNext.Enabled = true;
            if (dlBienSo.Visible.Equals(true))
            {
                if (dlBenChayCuoc.SelectedIndex > 0 && dlBienSo.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                    SearchByBenChayCuoc(1);
                else
                    if (dlBenChayCuoc.SelectedIndex.Equals(0) && dlBienSo.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                        SearchAll(1);
                    else
                        if (dlBenChayCuoc.SelectedIndex > 0 && dlBienSo.SelectedIndex > 0 && dlTramTron.SelectedIndex.Equals(0))
                            SearchByBienSo(1);
            }
            else
            {
                if (dlBenChayCuoc.SelectedIndex > 0 && dlBienSoText.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                    SearchByBenChayCuoc(1);
                else if (dlBenChayCuoc.SelectedIndex > 0 && dlBienSoText.SelectedIndex > 0 && dlTramTron.SelectedIndex.Equals(0))
                    SearchByBienSoText(1);
            }
        }
        else
            gstGetMess("Bạn không có quyền xem trang này", "Home.aspx");
    }
    private void SearchByBienSo(int page)
    {
        getDate();
        p = new SqlParameter[6];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@BienSo", ex.GetGuid(dlBienSo.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@PageNumber", page);
        p[3].SqlDbType = SqlDbType.Int;
        p[4] = new SqlParameter("@RowspPage", PageNumber);
        p[4].SqlDbType = SqlDbType.Int;
        p[5] = new SqlParameter("@BenChayCuoc", ex.GetGuid(dlBenChayCuoc.SelectedValue));
        p[5].SqlDbType = SqlDbType.UniqueIdentifier;

        GV.DataSource = ex.GetData("sp_BCNH_BSX_ByBenChayCuocBSX", p);
        GV.DataBind();

        p = new SqlParameter[4];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@BienSo", ex.GetGuid(dlBienSo.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@BenChayCuoc", ex.GetGuid(dlBenChayCuoc.SelectedValue));
        p[3].SqlDbType = SqlDbType.UniqueIdentifier;

        dt = ex.GetData("sp_BCNH_BSX_ByBenChayCuocBSX_GetFooter", p);
        LoadFooter();
    }
    private void SearchByBienSoText(int page)
    {
        getDate();
        p = new SqlParameter[6];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@BienSo", dlBienSoText.SelectedItem.Text);
        p[2].SqlDbType = SqlDbType.NVarChar;
        p[3] = new SqlParameter("@PageNumber", page);
        p[3].SqlDbType = SqlDbType.Int;
        p[4] = new SqlParameter("@RowspPage", PageNumber);
        p[4].SqlDbType = SqlDbType.Int;
        p[5] = new SqlParameter("@BenChayCuoc", ex.GetGuid(dlBenChayCuoc.SelectedValue));
        p[5].SqlDbType = SqlDbType.UniqueIdentifier;

        GV.DataSource = ex.GetData("sp_BCNH_BSX_ByBenChayCuocBSXText", p);
        GV.DataBind();

        p = new SqlParameter[4];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@BienSo", dlBienSoText.SelectedItem.Text);
        p[2].SqlDbType = SqlDbType.NVarChar;
        p[3] = new SqlParameter("@BenChayCuoc", ex.GetGuid(dlBenChayCuoc.SelectedValue));
        p[3].SqlDbType = SqlDbType.UniqueIdentifier;

        dt = ex.GetData("sp_BCNH_BSX_ByBenChayCuocBSXText_GetFooter", p);
        LoadFooter();
    }
    private void SearchAll(int page)
    {
        getDate();
        p = new SqlParameter[4];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;

        p[2] = new SqlParameter("@PageNumber", page);
        p[2].SqlDbType = SqlDbType.Int;
        p[3] = new SqlParameter("@RowspPage", PageNumber);
        p[3].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_BCNH_BSX_TongHop", p);
        GV.DataBind();

        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;


        dt = ex.GetData("sp_BCNH_TongHop_GetFooter", p);
        LoadFooter();
    }
    private void SearchByBenChayCuoc(int page)
    {
        getDate();
        p = new SqlParameter[5];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;

        p[2] = new SqlParameter("@PageNumber", page);
        p[2].SqlDbType = SqlDbType.Int;
        p[3] = new SqlParameter("@RowspPage", PageNumber);
        p[3].SqlDbType = SqlDbType.Int;
        p[4] = new SqlParameter("@BenChayCuoc", ex.GetGuid(dlBenChayCuoc.SelectedValue));
        p[4].SqlDbType = SqlDbType.UniqueIdentifier;


        GV.DataSource = ex.GetData("sp_BCNH_BSX_ByBenChayCuoc", p);
        GV.DataBind();

        p = new SqlParameter[3];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@BenChayCuoc", ex.GetGuid(dlBenChayCuoc.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;



        dt = ex.GetData("sp_BCNH_BSX_ByBenChayCuoc_GetFooter", p);
        LoadFooter();
    }
    private void LoadFooter()
    {
        if (dt.Rows.Count > 0)
        {
            tlhang = double.Parse(dt.Rows[0]["TLHang"].ToString());
            klsauquidoi = double.Parse(dt.Rows[0]["KLSauQuiDoi"].ToString());
            klthucte = double.Parse(dt.Rows[0]["KLThucTe"].ToString());
            klmuaVAT = double.Parse(dt.Rows[0]["KLMuaVAT"].ToString());
            klmuaKVAT = double.Parse(dt.Rows[0]["KLMuaKoVAT"].ToString());
            giavlvat = double.Parse(dt.Rows[0]["GiaVLCoVAT"].ToString());
            giavlkhongvat = double.Parse(dt.Rows[0]["GiaVLKhongVAT"].ToString());
            ttvlcovat = double.Parse(dt.Rows[0]["ThanhTienVLCoVAT"].ToString());
            ttvlkvat = double.Parse(dt.Rows[0]["ThanhTienVLKhongVAT"].ToString());
            cvccovat = double.Parse(dt.Rows[0]["GiaCuocCoVAT"].ToString());
            cvckvt = double.Parse(dt.Rows[0]["GiaCuocKhongVAT"].ToString());

            ttcvccovat = double.Parse(dt.Rows[0]["ThanhTienCuocVCCoVAT"].ToString());
            ttcvckvat = double.Parse(dt.Rows[0]["ThanhTienCuocVCKhongVAT"].ToString());
            tt1m3covat = double.Parse(dt.Rows[0]["ThanhTien1m3CoVAT"].ToString());
            tt1m3kvat = double.Parse(dt.Rows[0]["ThanhTien1m3KhongVAT"].ToString());
            tt1chuyencovat = double.Parse(dt.Rows[0]["ThanhTien1ChuyenCoVAT"].ToString());
            tt1chuyenkvat = double.Parse(dt.Rows[0]["ThanhTien1ChuyenKhongVAT"].ToString());
        }
        if (GV.Rows.Count > 0)
        {
            GV.FooterRow.Cells[3].Text = "Tổng cộng";
            GV.FooterRow.Cells[3].HorizontalAlign = HorizontalAlign.Center;
            GV.FooterRow.Cells[6].Text = ConvertDecimal(klmuaVAT);
            GV.FooterRow.Cells[7].Text = ConvertDecimal(klmuaKVAT);
            GV.FooterRow.Cells[8].Text = ConvertDecimal(klthucte);
            GV.FooterRow.Cells[9].Text = ConvertDecimal(tlhang);
            GV.FooterRow.Cells[10].Text = ConvertDecimal(klsauquidoi);
            GV.FooterRow.Cells[11].Text = ConvertDecimal(giavlvat);
            GV.FooterRow.Cells[12].Text = ConvertDecimal(giavlkhongvat);
            GV.FooterRow.Cells[13].Text = ConvertDecimal(ttvlcovat);
            GV.FooterRow.Cells[14].Text = ConvertDecimal(ttvlkvat);
            GV.FooterRow.Cells[15].Text = ConvertDecimal(cvccovat);
            GV.FooterRow.Cells[16].Text = ConvertDecimal(cvckvt);
            GV.FooterRow.Cells[17].Text = ConvertDecimal(ttcvccovat);
            GV.FooterRow.Cells[18].Text = ConvertDecimal(ttcvckvat);

            GV.FooterRow.Cells[19].Text = ConvertDecimal(tt1m3covat);
            GV.FooterRow.Cells[20].Text = ConvertDecimal(tt1m3kvat);
            GV.FooterRow.Cells[21].Text = ConvertDecimal(tt1chuyencovat);
            GV.FooterRow.Cells[22].Text = ConvertDecimal(tt1chuyenkvat);
        }
    }
    protected void btnPre_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;
            btnPre.Enabled = true;
            btnNext.Enabled = true;
            btnFirst.Enabled = true;

            if (dlBienSo.Visible.Equals(true))
            {
                if (dlBenChayCuoc.SelectedIndex > 0 && dlBienSo.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                    SearchByBenChayCuoc(CurrentPage);
                else
                    if (dlBenChayCuoc.SelectedIndex.Equals(0) && dlBienSo.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                        SearchAll(CurrentPage);
                    else
                        if (dlBenChayCuoc.SelectedIndex > 0 && dlBienSo.SelectedIndex > 0 && dlTramTron.SelectedIndex.Equals(0))
                            SearchByBienSo(CurrentPage);
            }
            else
            {
                if (dlBenChayCuoc.SelectedIndex > 0 && dlBienSoText.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                    SearchByBenChayCuoc(CurrentPage);
                else if (dlBenChayCuoc.SelectedIndex > 0 && dlBienSoText.SelectedIndex > 0 && dlTramTron.SelectedIndex.Equals(0))
                    SearchByBienSoText(CurrentPage);
            }
        }
        else
        {
            btnFirst.Enabled = false;
            btnPre.Enabled = false;
            btnNext.Enabled = true;
        }

        
    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        CurrentPage++;

        if (dlBienSo.Visible.Equals(true))
        {
            if (dlBenChayCuoc.SelectedIndex > 0 && dlBienSo.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                SearchByBenChayCuoc(CurrentPage);
            else
                if (dlBenChayCuoc.SelectedIndex.Equals(0) && dlBienSo.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                    SearchAll(CurrentPage);
                else
                    if (dlBenChayCuoc.SelectedIndex > 0 && dlBienSo.SelectedIndex > 0 && dlTramTron.SelectedIndex.Equals(0))
                        SearchByBienSo(CurrentPage);
        }
        else
        {
            if (dlBenChayCuoc.SelectedIndex > 0 && dlBienSoText.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                SearchByBenChayCuoc(CurrentPage);
            else if (dlBenChayCuoc.SelectedIndex > 0 && dlBienSoText.SelectedIndex > 0 && dlTramTron.SelectedIndex.Equals(0))
                SearchByBienSoText(CurrentPage);
        }

        if (GV.Rows.Count.Equals(0))
        {
            btnPre.Enabled = true;
            btnNext.Enabled = false;
        }
        else
        {
            btnPre.Enabled = true;
            btnNext.Enabled = true;
        }
        btnFirst.Enabled = true;

       
    }
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;

        if (dlBienSo.Visible.Equals(true))
        {
            if (dlBenChayCuoc.SelectedIndex > 0 && dlBienSo.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                SearchByBenChayCuoc(CurrentPage);
            else
                if (dlBenChayCuoc.SelectedIndex.Equals(0) && dlBienSo.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                    SearchAll(CurrentPage);
                else
                    if (dlBenChayCuoc.SelectedIndex > 0 && dlBienSo.SelectedIndex > 0 && dlTramTron.SelectedIndex.Equals(0))
                        SearchByBienSo(CurrentPage);
        }
        else
        {
            if (dlBenChayCuoc.SelectedIndex > 0 && dlBienSoText.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                SearchByBenChayCuoc(CurrentPage);
            else if (dlBenChayCuoc.SelectedIndex > 0 && dlBienSoText.SelectedIndex > 0 && dlTramTron.SelectedIndex.Equals(0))
                SearchByBienSoText(CurrentPage);
        }

        btnPre.Enabled = false;
        btnNext.Enabled = true;
        btnFirst.Enabled = false;

       
    }
    string ConvertDecimal(double s)
    {
        string q = "";
        if (s.ToString().Contains("."))
        {
            double x = double.Parse(s.ToString().Substring(0, s.ToString().IndexOf('.')));
            q = (x != 0 && x > 999) ? string.Format("{0:0,0}", x) : x.ToString();
            q += s.ToString().Substring(s.ToString().IndexOf('.'));
        }
        else
            q = (s != 0 && s > 999) ? string.Format("{0:0,0}", s) : s.ToString();
        return q;
    }
    protected void dlLoaiBaoCao_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlLoaiBaoCao.SelectedIndex.Equals(0))
            Response.Redirect("BCNHTongHop.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(1))
            Response.Redirect("BCNHBenChayCuoc.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(2))
            Response.Redirect("BCNHBenBan.aspx");
        else
            if (dlLoaiBaoCao.SelectedIndex.Equals(3))
                Response.Redirect("BCNHBienSoXe.aspx");
            else if (dlLoaiBaoCao.SelectedIndex.Equals(4))
                Response.Redirect("BCNHLoaiVL.aspx");
    }
    protected void dlBenChayCuoc_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlBenChayCuoc.SelectedItem.Text.Trim().ToLower().Contains("đức anh"))
        {
            LoadBienSo(dlBienSo, "--Chọn biển số--");
            dlBienSoText.Visible = false;
            dlBienSoText.SelectedIndex = 0;
            dlBienSo.Visible = true;
        }
        else
        {
            LoadBienSoText(dlBienSoText, "--Chọn biển số--");
            dlBienSoText.Visible = true;
            dlBienSo.SelectedIndex = 0;
            dlBienSo.Visible = false;
        }
    }
    //protected void btnKhoa_Click(object sender, EventArgs e)
    //{
    //    if (f_Khoa.Equals(1))
    //    {
    //        if (dlThang.SelectedIndex > 0)
    //        {
    //            p = new SqlParameter[4];
    //            p[0] = new SqlParameter("@Name", "BCNH");
    //            p[0].SqlDbType = SqlDbType.NVarChar;
    //            p[1] = new SqlParameter("@Thang", int.Parse(dlThang.SelectedValue));
    //            p[1].SqlDbType = SqlDbType.Int;
    //            p[2] = new SqlParameter("@Nam", int.Parse(dlNam.SelectedValue));
    //            p[2].SqlDbType = SqlDbType.Int;
    //            p[3] = new SqlParameter("@Khoa", btnKhoa.Text.Trim().ToLower().Equals("mở lại") ? 1 : 2);
    //            p[3].SqlDbType = SqlDbType.Int;
    //            ex.ExecuteNonQuerry("sp_xdakhoa_Update", p);

    //            if (btnKhoa.Text.Equals("Khóa lại"))
    //            {
    //                lbTrangThai.Text = "Trạng thái: Dữ liệu đang bị khóa";
    //                btnKhoa.Text = "Mở lại";
    //                gstGetMess("Đã khóa lại", "");


    //            }
    //            else
    //            {
    //                lbTrangThai.Text = "Trạng thái: Dữ liệu đang mở";
    //                btnKhoa.Text = "Khóa lại";
    //                gstGetMess("Đã mở lại", "");
    //            }

    //        }
    //        else gstGetMess("Chọn tháng cần khóa", "");
    //    }
    //    else gstGetMess("Bạn không có quyền khóa/mở dữ liệu", "");
    //}
    
}